﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Settings;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace PanasonicWarranty.Business
{
    public partial class pa_Customer : BaseClass
    {
        public static List<pa_Customer> Paging(int currentPage, int pageSize, ref int totalRecord,
                                            string phone, int? customerGroupId)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                var resutl = context.pa_Customers.AsQueryable();
                if (!string.IsNullOrWhiteSpace(phone))
                {
                    resutl = resutl.Where(p => p.CustomerPhone.Contains(phone));
                }
                if (customerGroupId != null)
                {
                    resutl = resutl.Where(p => p.CustomerGroupID == customerGroupId);
                }
                totalRecord = resutl.Count();
                return resutl.Skip(((currentPage - 1) < 0 ? 0 : (currentPage - 1)) * pageSize).Take(pageSize).ToList();
            }
        }

        public static string GetListCustomerPhone(string uploadfilePath)
        {
            try
            {
                string retValue = "";
                using (DataTable dtSource = new DataTable())
                {
                    using (OleDbConnection Oleconn = GetOleDbConnection(uploadfilePath))
                    {
                        Oleconn.Open();
                        var dtSchema = Oleconn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                        var sheet1 = dtSchema.Rows[0].Field<string>("TABLE_NAME");
                        using (OleDbCommand Olecmd = Oleconn.CreateCommand())
                        {
                            Olecmd.CommandText = string.Format(@"SELECT DISTINCT * FROM [{0}]", sheet1);
                            dtSource.Load(Olecmd.ExecuteReader());
                        }
                    }
                    foreach (DataRow row in dtSource.Rows)
                    {
                        retValue += "," + row[0];
                    }
                }
                if(retValue.Length > 0)
                    return retValue.Substring(1);
                return "";
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static List<pa_Customer> GetByCustomerGroup(int? groupId)
        {
            using (var context = new DBWarrantyDataContext(AppSetting.ConnectionString))
            {
                if (groupId == null)
                    return context.pa_Customers.Where(p => p.CustomerGroupID == null && p.Disabled == false).ToList();
                else
                    return context.pa_Customers.Where(p => p.CustomerGroupID == groupId && p.Disabled == false).ToList();
            }
        }
    }
}
